package com.hjl.dao.impl;

import com.hjl.dao.EmployeeDao;
import com.hjl.domain.Emp_Dept_Job_inf;
import com.hjl.domain.Employee_inf;
import com.hjl.util.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * 员工数据访问层
 *
 * @author Lenovo
 * @version 1.0
 * @date 2021/10/8 13:57
 */
public class EmployeeDaoImpl implements EmployeeDao {
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());

    /**
     * 查找总页数
     *
     * @param condition 状态
     * @return int
     * @author Lenovo
     * @date 2021/10/8 14:09
     */
    @Override
    public int findTotalCount(Map<String, String[]> condition) {
        String sql = "select count(id) from employee_inf where 1=1 ";
        StringBuilder sb = new StringBuilder(sql);
        Set<String> keySet = condition.keySet();
        List<Object> params = new ArrayList<>();
        for (String key : keySet) {
            if ("currentPage".equals(key) || "rows".equals(key)) {
                continue;
            }
            String value = condition.get(key)[0];
            if (value != null && !"".equals(value)) {
                sb.append(" and ").append(key).append(" like ? ");
                params.add("%" + value + "%");
            }
        }
        return template.queryForObject(sb.toString(), Integer.class, params.toArray());

    }

    /**
     * 查找当前页的数据集合
     *
     * @param start     起始页
     * @param rows      每页记录数
     * @param condition 状态
     * @return java.util.List<com.hjl.domain.Emp_Dept_Job_inf>
     * @author Lenovo
     * @date 2021/10/8 14:01
     */
    @Override
    public List<Emp_Dept_Job_inf> findByPage(int start, int rows, Map<String, String[]> condition) {
        //多表查询
        String sql = "select * from\n" +
                "    (\n" +
                "        select e.*,j.NAME as job_name from\n" +
                "            (select e.*,d.NAME as dept_name  from employee_inf e left join dept_inf d on e.DEPT_ID=d.ID) e\n" +
                "                left join job_inf j on  e.JOB_ID=j.ID\n" +
                "    ) a" +
                " where 1 = 1 ";
        StringBuilder sb = new StringBuilder(sql);
        Set<String> keySet = condition.keySet();
        List<Object> params = new ArrayList<>();
        for (String key : keySet) {
            if ("currentPage".equals(key) || "rows".equals(key)) {
                continue;
            }
            String value = condition.get(key)[0];
            if (value != null && !"".equals(value)) {
                sb.append(" and ").append(key).append(" like ? ");
                params.add("%" + value + "%");
            }
        }
        sb.append(" limit ? , ?");
        params.add(start);
        params.add(rows);
        System.out.println(sb.toString());
        System.out.println(params);
        return template.query(sb.toString(), new BeanPropertyRowMapper<>(Emp_Dept_Job_inf.class), params.toArray());
    }

    /**
     * 删除
     *
     * @param id ID
     * @author Lenovo
     * @date 2021/10/8 14:09
     */
    @Override
    public void delEmployee(int id) {
        String sql = "delete from employee_inf where ID = ?";
        template.update(sql, id);
    }

    /**
     * 通过id查找（用于编辑）
     *
     * @param id ID
     * @return com.hjl.domain.Emp_Dept_Job_inf
     * @author Lenovo
     * @date 2021/10/8 14:10
     */
    @Override
    public Emp_Dept_Job_inf findEmployeeById(int id) {
        String sql = "select * from\n" +
                "    (\n" +
                "        select e.*,j.NAME as job_name from\n" +
                "            (select e.*,d.NAME as dept_name  from employee_inf e left join dept_inf d on e.DEPT_ID=d.ID) e\n" +
                "                left join job_inf j on  e.JOB_ID=j.ID\n" +
                "    ) a" +
                " where id=?";
        return template.queryForObject(sql, new BeanPropertyRowMapper<>(Emp_Dept_Job_inf.class), id);
    }

    /**
     * 更新
     *
     * @param e 员工对象
     * @author Lenovo
     * @date 2021/10/8 14:11
     */
    @Override
    public void updateEmployee(Employee_inf e) {
        String sql = "update employee_inf set NAME=?,CARD_ID=?,SEX=?,JOB_ID=?,EDUCATION=?,EMAIL=?," +
                "PHONE=?,TEL=?,PARTY=?,QQ_NUM=?,ADDRESS=?,POST_CODE=?,BIRTHDAY=?," +
                "RACE=?,SPECIALITY=?,HOBBY=?,REMARK=?,DEPT_ID=? where ID=? ";
        template.update(sql, e.getName(), e.getCard_id(),
                e.getSex(), e.getJob_id(),
                e.getEducation(), e.getEmail(),
                e.getPhone(), e.getTel(), e.getParty(), e.getQq_num(), e.getAddress(),
                e.getPost_code(), e.getBirthday(), e.getRace(), e.getSpeciality(),
                e.getHobby(), e.getRemark(), e.getDept_id(), e.getId());
    }

    /**
     * 添加
     *
     * @param e 员工对象
     * @author Lenovo
     * @date 2021/10/8 14:11
     */
    @Override
    public void addEmployee(Employee_inf e) {
        String sql = "insert into employee_inf values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        template.update(sql, e.getDept_id(), e.getJob_id(), e.getName(), e.getCard_id(), e.getAddress(),
                e.getPost_code(), e.getTel(), e.getPhone(), e.getQq_num(), e.getEmail(), e.getSex(),
                e.getParty(), e.getBirthday(), e.getRace(), e.getEducation(), e.getSpeciality(),
                e.getHobby(), e.getRemark(), e.getCreatedate());
    }

    /**
     * 查找所有
     *
     * @return java.util.List<com.hjl.domain.Emp_Dept_Job_inf>
     * @author Lenovo
     * @date 2021/10/8 14:11
     */
    @Override
    public List<Emp_Dept_Job_inf> findAllEmployee() {
        String sql = "select * from\n" +
                "    (\n" +
                "        select e.*,j.NAME as job_name from\n" +
                "            (select e.*,d.NAME as dept_name  from employee_inf e left join dept_inf d on e.DEPT_ID=d.ID) e\n" +
                "                left join job_inf j on  e.JOB_ID=j.ID\n" +
                "    ) a";
        return template.query(sql, new BeanPropertyRowMapper<>(Emp_Dept_Job_inf.class));
    }

    /**
     * 查找所有身份证号
     *
     * @return java.util.List<java.lang.String>
     * @author Lenovo
     * @date 2021/10/8 14:12
     */
    @Override
    public List<String> findCardIds() {
        String sql = "select CARD_ID from employee_inf";
        return template.queryForList(sql, String.class);
    }

    /**
     * 通过id查找身份证号
     *
     * @param id ID
     * @return java.util.List<java.lang.String>
     * @author Lenovo
     * @date 2021/10/8 14:12
     */
    @Override
    public List<String> findCardIds(String id) {
        String sql = "select CARD_ID from employee_inf where CARD_ID != ?";
        return template.queryForList(sql, String.class, id);
    }
}
